Overview of Transaction

These transactions tracked purchases over a two year period in 7 states and total over 5197681 specific product purchases were recorded. Total 5197681 specific items purchases were recorded across 387 unique stores. A total of 927 different products within the four commodities were recorded during this time period.

number_of_unique_values number_of_missing_values
upc 927 0
dollar_sales 1502 0
units 49 0
time_of_transaction 1440 0
geography 2 0
week 104 0
household 510027 0
store 387 0
basket 3316349 0
day 726 0
coupon 2 0

Transactions over Time

The following graph suggests that the quantity of items purchase is comparably consistent in two-year.The purchases reach a spike in week 17. Some obvious changes could be observed between week 32 and week 44.

Transactions across States

The following table show the number of household in each state:

state total_household
GA 233976
KY 112875
TN 109339
SC 23085
AL 15159
IL 12358
IN 10862

Each state shows consistency in items purchases in this two-year. The items purchase in GA, KY and TN are much higher than remaining states, which is also consistent with the number of household.

Through the deeper observations, we could find that in week 12, purchases in GA dropped greatly in second year. In week 27, purchases in KY and TN increased greatly. Further investigation is required to adjust strategy accordingly.

Transactions for Each Commodity

Each commodity shows consistency in items purchases in this two-year. The items purchase in pasta and pasta sauce are much higher than pancake mix and syrups.

Shoppers purchased pasta and pasta sauce in preference to panckae and syrup arcoss states.

The purchase of pancake mixes and syrups are very stable across a year. The purchase of pasta is comparably stable. However, the purchase of pasta sauce reach two spikes in a year, causing the spikes for total purchase. Since purchase of pasta and pasta sauce do come in hand by hand, it is surprising that the purchase of pasta do not increase greatly during that period. Further research is required for better management.

The above observation is found across states.

Penetration

Household Product Penetration

The household penetration of each product within each commodity shows us a simple view of preferences within our grocery shopper population. There are a total 510027 distinct households within this data set, and the following table shows how many distinct households within this total actually shopped in each of our four commodities.

commodity unique_household_count percentage_household
pasta 411601 0.81
pasta sauce 362305 0.71
syrups 256514 0.50
pancake mixes 130580 0.26

Pasta

The following table shows the product/brand penetration of the Pasta commodity group. It looks like our grocery shoppers are more price conscious within this area and that the Private Label manufacturers have a solid market share of the sales.

brand unique_household_count percentage_household
Private Label 267358 0.52
Barilla 125579 0.25
Mueller 91128 0.18
Creamette 86672 0.17
Private Label Premium 76558 0.15

Pasta Sauce

Contrary to the Pasta commodity finding, the Private Label brand does not have control of the market share within this group; it holds the third spot in which only about 25% of the shoppers that do buy pasta sauce tend to buy the Private Label brand. Since Pasta and Pasta Sauce do go hand in hand, the Private Label brand managers may have an opportunity within this area to do cross-commodity marketing.

brand unique_household_count percentage_household
Ragu 207237 0.41
Prego 118132 0.23
Private Label 90754 0.18
Hunt’s 69509 0.14
Classico 58865 0.12

Pancake Mixes

Similar with Pasta Sauce commodity finding, the Private Label brand does not have control of the market share within this group; it is the third place in which only about 25% of the shoppers that do buy pancake mixes do buy the Private Label brand. Some opportunites within this commodity could be grabbed.

brand unique_household_count percentage_household
Aunt Jemima 56103 0.11
Hungry Jack 35557 0.07
Private Label 32199 0.06
Krusteaz 9241 0.02
Bisquick 7021 0.01

Syrups

The Private Label dominates this commodity. Thus, Private Label brand managers may consider there are cross-commodity marketing opportunities within this area as well as within the Pasta and Pasta Sauce groups.

brand unique_household_count percentage_household
Private Label 106181 0.21
Aunt Jemima 65654 0.13
Karo 30713 0.06
Mrs Butterworth 31809 0.06
Northwoods 29456 0.06

Dollar Sales and Number of Purchases

Commodity

Even though shoppers purchase pasta more than pasta sauce, the dollar sales of pasta sauce are much higher than pasta, given the unit price. Store managers may think about increasing shoppers chocies about pasata sauce. It is also the same case with syrups.

commodity total_purchases total_units total_sales avg_pay avg_unit_price
pasta sauce 1913522 2313168 4025026.5 2.103465 1.740049
pasta 2327944 2880837 2851930.6 1.225086 0.989966
syrups 685918 724976 1741308.5 2.538654 2.401884
pancake mixes 270297 300646 511814.3 1.893526 1.702382

Pasta

The following table shows top 5 pasta products (based on total sales). Products of Private label dominates this commodity. Shoppers do not favor the cheapest one. Pasta managers may pay more attentions on shoppers peference in pasta type rather than price.

product_description total_purchases total_units total_sales avg_pay avg_unit_price
PRIVATE LABEL SPAGHETTI REGULAR 171884 215816 184111.72 1.0711394 0.8530958
PRIVATE LABEL THIN SPAGHETTI 173093 228331 148208.36 0.8562354 0.6490943
PRIVATE LABEL ELBOW MACARONI 99065 118911 123143.67 1.2430593 1.0355953
PRIVATE LABEL ANGEL HAIR PASTA 79920 111764 66160.21 0.8278305 0.5919635
PRIVATE LABEL SPAGHETTI THIN 46344 51599 61573.90 1.3286272 1.1933158

The following table show top 5 pasta brands (based on total sales), which is slight different from observations of penetration. Ronzoni is not in top 5 in penetration but has better dollar sales. Ronzoni managers should think about acquring more shoppers to increase penetration. Besides, almost half of purchases are generated by Private Label, which is greatly unbalanced.

brand total_purchases purchase_percentage total_units total_sales avg_pay avg_unit_price
Private Label 998961 0.43 1269263 995201.3 0.9962363 0.7840781
Barilla 322649 0.14 394454 433764.8 1.3443859 1.0996587
Creamette 211008 0.09 254572 271543.3 1.2868863 1.0666661
Mueller 203244 0.09 250389 270701.2 1.3319027 1.0811227
Ronzoni 152712 0.07 188376 213566.5 1.3984918 1.1337245

The following table show top 3 pasta brands (based on total sales) across states. Private Label is a dominator. Barilla and Creamette also have great performance in most states. Mangers of them may think about expansion strategy in other states.

state brand total_purchases total_units total_sales avg_pay avg_unit_price
AL Private Label 23440 29334 21893.68 0.9340307 0.7463585
AL Barilla 6708 7898 9315.96 1.3887835 1.1795341
AL Creamette 5223 6102 7367.35 1.4105591 1.2073664
GA Private Label 406191 523881 393146.53 0.9678859 0.7504501
GA Barilla 155485 188829 221006.19 1.4213988 1.1704039
GA Mueller 168400 207566 219824.06 1.3053685 1.0590562
IL Private Label 34112 42532 35695.89 1.0464320 0.8392714
IL Creamette 8383 10076 10837.77 1.2928272 1.0756024
IL Private Label Premium 5900 6962 7164.53 1.2143271 1.0290908
IN Private Label 24490 30777 25176.11 1.0280159 0.8180170
IN Creamette 7353 8959 9332.62 1.2692262 1.0417033
IN San Giorgio 5018 6128 6893.08 1.3736708 1.1248499
KY Private Label 291819 364969 294950.79 1.0107320 0.8081530
KY Creamette 94971 114579 120535.81 1.2691854 1.0519887
KY Barilla 62346 77169 76964.50 1.2344737 0.9973500
SC Private Label 22079 28161 21095.71 0.9554649 0.7491108
SC Barilla 9702 11803 13735.80 1.4157699 1.1637550
SC Mueller 9258 11357 12295.49 1.3280935 1.0826354
TN Private Label 196830 249609 203242.55 1.0325791 0.8142437
TN Creamette 94912 114645 123239.05 1.2984559 1.0749623
TN Barilla 78263 96177 100013.11 1.2779105 1.0398859

The following geo-graph shows more details about pasta sales in different stores (based on zipcode):

Pasta Sauce

The following table show top 5 pasta sauce products (based on total sales).

product_description total_purchases total_units total_sales avg_pay avg_unit_price
RAGU TRADITIONAL PLAIN 79305 100617 146471.95 1.846945 1.455738
PREGO REG SPAGHETTI SAUCE 58660 72940 125166.65 2.133765 1.716022
RAGU OWS SPAG SAUCE MEAT 48643 62756 89174.23 1.833239 1.420967
PREGO MUSHROOM SPAG SAUCE 34807 41153 84147.56 2.417547 2.044749
RAGU SPAGH SAUCE PLAIN 24508 28335 80414.19 3.281140 2.837981

The following table show top 5 pasta sauce brands (based on total sales).Private Label is the fourth place. The Private Label brand managers may have an opportunity within this area to do cross-commodity marketing. Besides, Bertolli managers may consider acquring more new shoppers to increase penetration.

brand total_purchases purchase_percentage total_units total_sales avg_pay avg_unit_price
Ragu 738294 0.39 891433 1500754.6 2.032733 1.683530
Prego 342971 0.18 402897 799932.2 2.332361 1.985451
Classico 148032 0.08 172242 438178.5 2.960025 2.543970
Private Label 245385 0.13 306059 331062.4 1.349155 1.081695
Bertolli 84905 0.04 97379 258090.8 3.039760 2.650375

The following table show top 3 pasta sauce brands (based on total sales) across states. The Private Label brand managers should pay attentions to GA,KY,and TN market, where have amount of pasta shoppers, but shoppers there do not favor Private Label pasta sauce.

state brand total_purchases total_units total_sales avg_pay avg_unit_price
AL Ragu 16076 19003 31434.15 1.955347 1.654168
AL Prego 7506 8449 16414.21 2.186812 1.942740
AL Private Label 7449 9381 9699.15 1.302074 1.033914
GA Ragu 312983 369749 636282.61 2.032962 1.720850
GA Prego 155985 179089 352789.17 2.261687 1.969910
GA Classico 73215 85273 211950.30 2.894903 2.485550
IL Ragu 22390 28744 46191.26 2.063031 1.606988
IL Prego 9609 12050 24188.26 2.517250 2.007325
IL Private Label 7960 10474 10645.97 1.337433 1.016419
IN Ragu 15431 19463 31835.84 2.063109 1.635711
IN Prego 8039 9859 19737.25 2.455187 2.001952
IN Classico 2072 2392 6339.34 3.059527 2.650226
KY Ragu 197233 243808 401958.72 2.037989 1.648669
KY Prego 91291 110456 223141.09 2.444284 2.020181
KY Classico 30644 35502 94109.73 3.071066 2.650829
SC Ragu 15947 19129 32404.88 2.032036 1.694019
SC Prego 7843 8991 17687.40 2.255183 1.967234
SC Classico 4254 5097 12742.88 2.995505 2.500075
TN Ragu 158234 191537 320647.12 2.026411 1.674074
TN Prego 62698 74003 145974.85 2.328222 1.972553
TN Classico 32544 37800 97657.40 3.000780 2.583529

The following geo-graph shows more details about pasta sauce sales in different stores (based on zipcode):

Pancake Mixes

The following table show top 5 pancake mix products (based on total sales).

product_description total_purchases total_units total_sales avg_pay avg_unit_price
AJ BUTTERMILK PANCAKE MIX 43462 45595 93321.06 2.147187 2.046739
PRIVATE LABEL COMPLETE PANCAKE MIX 52095 58396 73616.82 1.413126 1.260648
AUNT JEM ORIGINAL PANCAKE MIX 25542 26759 55974.63 2.191474 2.091806
A/JEM COMPLETE PANCAKE MI 22829 23963 50030.20 2.191520 2.087810
H J PANCK BTRMLK COMP MIX 21504 22569 44004.65 2.046347 1.949783

The following table show top 5 pancake mix brands (based on total sales). Aunt Jemima is a solid dominator in this area. Private Label managers may think about differentiation strategy to earn the market share.

brand total_purchases purchase_percentage total_units total_sales avg_pay avg_unit_price
Aunt Jemima 97264 0.36 101853 206698.12 2.125125 2.029377
Hungry Jack 54633 0.20 57016 111435.32 2.039707 1.954457
Private Label 52095 0.19 58396 73616.82 1.413126 1.260648
Krusteaz 13910 0.05 14796 30528.77 2.194735 2.063312
Bisquick 12674 0.05 17675 18437.72 1.454767 1.043152

The following table show top 3 pancake mix brands (based on total sales) across states. The ranking is surprisingly consistent across states. Other brands face great challenges in this commodity. Thus other brands managers may think about starting the challenge in one or two states instead of competing in every states.

state brand total_purchases total_units total_sales avg_pay avg_unit_price
AL Aunt Jemima 2002 2080 4129.48 2.062677 1.985327
AL Hungry Jack 1177 1231 2222.45 1.888233 1.805402
AL Private Label 1372 1506 1852.83 1.350459 1.230299
GA Aunt Jemima 53833 56894 112964.53 2.098425 1.985526
GA Hungry Jack 25108 26233 51116.34 2.035859 1.948551
GA Private Label 25231 28705 36021.29 1.427660 1.254879
IL Aunt Jemima 1584 1639 3571.41 2.254678 2.179018
IL Hungry Jack 1366 1419 2947.00 2.157394 2.076815
IL Private Label 1635 1748 2284.58 1.397297 1.306968
IN Aunt Jemima 1536 1578 3467.17 2.257272 2.197193
IN Hungry Jack 1010 1064 2116.38 2.095426 1.989079
IN Private Label 1011 1154 1505.30 1.488922 1.304419
KY Aunt Jemima 17566 18137 39452.10 2.245935 2.175227
KY Hungry Jack 14348 14854 29900.65 2.083959 2.012970
KY Private Label 12796 14020 17920.14 1.400449 1.278184
SC Aunt Jemima 2602 2724 5399.61 2.075177 1.982236
SC Hungry Jack 1183 1215 2410.56 2.037667 1.984000
SC Private Label 1472 1732 2115.77 1.437344 1.221576
TN Aunt Jemima 18141 18801 37713.82 2.078927 2.005948
TN Hungry Jack 10441 11000 20721.94 1.984670 1.883813
TN Private Label 8578 9531 11916.91 1.389241 1.250331

The following geo-graph shows more details about pancake mix sales in different stores (based on zipcode):

Syrups

The following table show top 5 syrups products (based on total sales).

product_description total_purchases total_units total_sales avg_pay avg_unit_price
AUNT JEMIMA ORIGINL SYRUP 31765 32385 98922.68 3.114204 3.054583
PRIVATE LABEL SYRUP PLASTIC BOTTLE 59460 64553 96146.78 1.616999 1.489424
MRS BUTTERWORTH SYRUP 28669 29468 86863.46 3.029874 2.947722
AUNT JEMIMA LITE SYRUP 19372 19884 67371.03 3.477753 3.388203
PRIVATE LABEL MAPLE SYRUP 14164 15463 66622.01 4.703615 4.308479

The following table show top 5 syrups brands (based on total sales). Private Label is neck to neck with Aunt Jemima. Aunt Jemima managers should consider increasing penetration to boost the total purchases. The same advise for Log Cabin, since their penetration has potential to be increased.

brand total_purchases purchase_percentage total_units total_sales avg_pay avg_unit_price
Aunt Jemima 120901 0.18 123938 359668.9 2.974904 2.902007
Private Label 206115 0.30 222524 352930.9 1.712301 1.586035
Mrs Butterworth 51197 0.07 52596 159165.5 3.108883 3.026190
Log Cabin 41172 0.06 42503 130904.4 3.179453 3.079887
Karo 47488 0.07 52004 120431.0 2.536030 2.315802

The following table show top 3 syrups brands (based on total sales) across states.Aunt Jemima should pay more attentions to IL market, since it is neck to neck with Mrs Butterworth.

state brand total_purchases total_units total_sales avg_pay avg_unit_price
AL Private Label 5002 5393 8762.19 1.751737 1.624734
AL Aunt Jemima 2960 3052 8672.49 2.929895 2.841576
AL Karo 1692 1877 3934.77 2.325514 2.096308
GA Aunt Jemima 70145 71717 200190.77 2.853956 2.791399
GA Private Label 80884 88639 142815.21 1.765679 1.611201
GA Northwoods 43148 45022 79111.65 1.833495 1.757178
IL Private Label 6854 7367 11445.80 1.669945 1.553658
IL Mrs Butterworth 2039 2098 6610.53 3.242045 3.150872
IL Aunt Jemima 1848 1945 6103.60 3.302814 3.138098
IN Private Label 4432 4724 7114.83 1.605332 1.506103
IN Aunt Jemima 1493 1552 4950.85 3.316041 3.189981
IN Mrs Butterworth 1211 1238 3877.39 3.201808 3.131979
KY Private Label 58257 62051 95525.54 1.639726 1.539468
KY Aunt Jemima 19065 19582 61451.28 3.223251 3.138151
KY Mrs Butterworth 15682 16165 50077.33 3.193300 3.097886
SC Aunt Jemima 3740 3830 10575.88 2.827775 2.761326
SC Private Label 4933 5561 8727.10 1.769126 1.569340
SC Log Cabin 1197 1220 3588.95 2.998287 2.941762
TN Private Label 45753 48789 78540.19 1.716613 1.609793
TN Aunt Jemima 21650 22260 67724.05 3.128132 3.042410
TN Mrs Butterworth 10945 11186 33833.17 3.091199 3.024599

The following geo-graph shows more details about syrups sales in different stores (based on zipcode):

Repeat Purchase Rate

The formula for repeat purchase rate is: \[ Repeat Purchase Rate = Number of Repeat Shoppers/ Number of Total Shoppers \]

Pasta

Repeat purchase rate for pasta is 0.65. Each shopper purchase pasta averagely 5.

We consider shoppers who purchase more than the 5 are frequent shoppers. Following table shows top 5 brands with most percentage of frequent shoppers. Dollar sales of Mlinotst and Private Label heavily rely on frequent shoppers.

brand frequent_percentage sales_percentage
Mlinotst 0.33 0.79
Private Label 0.21 0.59
China Mandarin 0.14 0.30
Barilla 0.10 0.40
Creamette 0.10 0.38

Pasta Sauce

Repeat purchase rate for pasta_sauce is 0.62.Each shopper purchase pasta averagely 5.

We consider shoppers who purchase more than the 5 are frequent shoppers. Following table shows top 5 brands with most percentage of frequent shoppers.Even though the percentage of frequent shoppers is not higt, the sales are still heavily rely on them.

brand frequent_percentage sales_percentage
Ragu 0.17 0.54
Prego 0.12 0.47
Hunt’s 0.10 0.39
Private Label 0.10 0.38
Chef Pizza 0.09 0.43

Pancake Mixes

Repeat purchase rate for pancake_mix is 0.39. Each shopper purchase pancake mixes averagely 3.

We consider shoppers who purchase more than the 3 are frequent shoppers. Following table shows top 5 brands with most percentage of frequent shoppers. Even though the percentage of frequent shoppers is not higt, the sales are still heavily rely on them.

brand frequent_percentage sales_percentage
Classique 0.16 0.55
Aunt Jemima 0.15 0.42
Bisquick 0.15 0.46
Bruce’s 0.15 0.50
White Lily 0.14 0.49

Syrups

Repeat purchase rate for syrups is 0.49.Each shopper purchase syrups averagely 3.

We consider shoppers who purchase more than the 3 are frequent shoppers. Following table shows top 5 brands with most percentage frequent shoppers. Even though the percentage of frequent shoppers is not higt, the sales are still heavily rely on them.

brand frequent_percentage sales_percentage
Tree of Life 0.33 0.54
Private Label 0.18 0.49
Aunt Jemima 0.15 0.47
Hungry Jack 0.15 0.48
Log Cabin 0.14 0.44

Generally, most shoppers repeatly purchase pasta and pasta sauce,and their repeat purchase rate is similar. But shoppers purchaes syrups more repeatly than panckae mixes. It suggests that shoppers consume syrups faster than pancake mixes, which provide a great opportunity for syrups brands. Through observations on frequent shoppers arcoss brands for each commodity, they make great contributions to sales.

Coupon

Brands with Coupons

Here is the overview of coupon distributions for each commodity.

commodity have_coupon total_brand coupon_percentage
pancake mixes 10 17 0.59
pasta 18 56 0.32
pasta sauce 12 43 0.28
syrups 17 35 0.49

Coupons over Time and States

The pasta sauce coupon change over time is very consistent with previous purchases observation, and it is also stable in two-year period. But it is not the case for pasta, syrups, and pancake mixes. It seems that retailers or suppliers make different attempts about when to distribute coupons. Based on previous purchases observations, it seems that drop in coupons do not change purchase a lot. However, more research about threshold (minimum coupons) should be conducted.

For pasta and pasta sauce, difference in coupons distribution is not great in each state. But it is not the same case for syrups and pancake mixes. For each state, they made diffferent efforts.

Coupon Users

54860 are actual coupon users; meaning, that they used a coupon at one time or another during any one of their shopping trips in this 2-year period. Only about 0.05 of our population uses coupons. The marketing brand managers are reaching a small pool of customers.

commodity total_coupon_users user_percentage
pancake mixes 4067 0.03
pasta 11875 0.03
pasta sauce 24983 0.06
syrups 13935 0.05

The following table shows top 5 brands for each commodity (based on coupon user percentage).Popular brands, such as Private Label, do not have high coupon user percentage.

commodity brand total_coupon_users user_percentage
pancake mixes Pioneer 367 0.17
pancake mixes Hungry Jack 3114 0.09
pancake mixes White Lily 159 0.02
pancake mixes Aunt Jemima 458 0.01
pancake mixes Bisquick 66 0.01
pancake mixes M W Flapstax 7 0.01
pasta Wacky Mac 21 0.27
pasta No Yolks 1735 0.06
pasta Barilla 4811 0.04
pasta Ronzoni 3155 0.04
pasta San Giorgio 1458 0.03
pasta sauce Bertolli 4651 0.11
pasta sauce Ragu 14947 0.07
pasta sauce Prego 6813 0.06
pasta sauce Newman’s 1063 0.05
pasta sauce Classico 2123 0.04
syrups Northwoods 4692 0.15
syrups Hungry Jack 2168 0.11
syrups Alaga 165 0.07
syrups Aunt Jemima 4553 0.07
syrups Log Cabin 1749 0.07

Coupons Users vs. Frequent Shoppers

Pasta

For pasta purchase, this suggests that coupon usage will lead to frequent purchase although further studies are required to confirm this.

## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  pasta_table
## X-squared = 1769, df = 1, p-value < 0.00000000000000022

Pasta Sauce

For pasta sauce purchase, this suggests that coupon usage will not lead to frequent purchase although further studies are required to confirm this.

## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  pasta_sauce_table
## X-squared = 1.944, df = 1, p-value = 0.1632

Pancake Mixes

For pancake mixes purchase, this suggests that coupon usage will lead to frequent purchase although further studies are required to confirm this.

## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  pancake_mix_table
## X-squared = 3.9119, df = 1, p-value = 0.04795

Syrups

For syrups purchase, this suggests that coupon usage will lead to frequent purchase although further studies are required to confirm this.

## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  syrups_table
## X-squared = 141.76, df = 1, p-value < 0.00000000000000022

In short, coupon usages do lead to frequent purchases for most commodities.

Purchase Together

The following graph is top 20 frequent purchase brands.

Since most brands cover multiple commoditis, we picked three brands based on previous analysis to observe what other brands are frequently purchased with them together.

Private Label

For Private Label, except for itself, associated brands are {Prego} and {Barilla}.

lhs rhs support confidence lift count
[1] {} => {Private Label} 0.3613361 0.3613361 1.0000000 1198317
[2] {Prego} => {Private Label} 0.0247064 0.2746059 0.7599737 81935
[3] {Barilla} => {Private Label} 0.0108137 0.1139989 0.3154927 35862

Ragu

For Ragu, associated brands are {Mueller} and {Creamette}.

lhs rhs support confidence lift count
[1] {Mueller} => {Ragu} 0.0120189 0.2173894 1.180623 39859
[2] {Creamette} => {Ragu} 0.0112033 0.1944472 1.056026 37154
[3] {} => {Ragu} 0.1841310 0.1841310 1.000000 610643